package batch.makeFile;

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang.ObjectUtils;
import org.apache.log4j.*;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.io.*;
import java.util.Date;

import batch.common.db.ConnectionManager;

public class makeUserFileForFra
{
    final String TODAY = DateFormatUtils.format(new Date(), "yyyyMMdd");

    public makeUserFileForFra() {
        ;
    }

    public static void main(String [] args) {
        Logger.getLogger("makefile.makeUserFileForFra").info("( * ) Start.");

        makeUserFileForFra mkRus = new makeUserFileForFra();
        mkRus.startMake();

        Logger.getLogger("makefile.makeUserFileForFra").info("( * ) End.");
    }

    //Make a log file and a data file.
    public void startMake(){
        Connection   conn = null;
        Statement    stmt = null;
        ResultSet    rs   = null;
        ResultSetMetaData  rsmd = null;
        OutputStreamWriter oswFile = null;

        String fileNm1  = "/home01/sfc/batch/makeFile/data/fra_member_"  + TODAY + ".txt";
        String fileNm2  = "/home01/sfc/batch/makeFile/data/fra_point_history_"  + TODAY + ".txt";
        String fileNm3  = "/home01/sfc/batch/makeFile/data/fra_point_table_"  + TODAY + ".txt";
        String fileNm4  = "/home01/sfc/batch/makeFile/data/fra_buy_history_"  + TODAY + ".txt";

        int cnt = 0;
        int rsCnt = 0;

        StringBuffer sbTemp = new StringBuffer();

        StringBuffer sQuery = new StringBuffer()
                             .append("SELECT ID                                             \n")
                             .append("     , REPLACE(REPLACE(FIRSTNAME, CHR(10), ''), CHR(13), '') FIRSTNAME \n")
                             .append("     , REPLACE(REPLACE(LASTNAME, CHR(10), ''), CHR(13), '')  LASTNAME  \n")
                             .append("     , GENDER                                         \n")
                             .append("     , BIRTH                                          \n")
                             .append("     , REPLACE(REPLACE(ADDRESS, CHR(10), ''), CHR(13), '')   ADDRESS   \n")
                             .append("     , COUNTRY                                        \n")
                             .append("     , REPLACE(REPLACE(POSTCODE, CHR(10), ''), CHR(13), '')  POSTCODE  \n")
                             .append("     , REPLACE(REPLACE(EMAIL, CHR(10), ''), CHR(13), '')     EMAIL     \n")
                             .append("     , NEWS_LETTER                                    \n")
                             .append("     , HP_BRAND                                       \n")
                             .append("     , HP_MODEL                                       \n")
                             .append("     , HP_IMEI                                        \n")
                             .append("     , HP_NUM                                         \n")
                             .append("     , HP_PROVIDER                                    \n")
                             .append("     , HP_BUY_YM                                      \n")
                             .append("     , GRADE                                          \n")
                             .append("     , TO_CHAR(CREATED, 'YYYYMMDDHH24MISS') CREATED   \n")
                             .append("     , TO_CHAR(MODIFIED, 'YYYYMMDDHH24MISS') MODIFIED \n")
                             .append("     , TO_CHAR(DELETED, 'YYYYMMDDHH24MISS') DELETED   \n")
                             .append("     , POINT                                          \n")
                             .append("     , POWER                                          \n")
                             .append("     , MARITAL                                        \n")
                             .append("     , OCCUPATION                                     \n")
                             .append("     , INCOME                                         \n")
                             .append("     , LANGUAGE                                       \n")
                             .append("     , COUNTRYCODE                                    \n")
                             .append("     , REG_COUNTRY                                    \n")
                             .append("     , MEMBERGBN                                      \n")
                             .append("     , HP_SN                                          \n")
                             .append("     , OTA_HOME_CD                                    \n")
                             .append("     , OTA_LOGIN_CD                                   \n")
                             .append("     , OTA_CS_NM                                      \n")
                             .append("     , OTA_CREATE_YMD                                 \n")
                             .append("     , RESERVED3                                      \n")
                             .append("     , PASSWORD_NEW                                   \n")
                             .append("  FROM G_MB_MEMBER                                    \n")
                             .append(" WHERE REG_COUNTRY = 'fra'                            \n")
                             .append("   AND COUNTRY = '0102017'                            \n")
                             .append("   AND CREATED > TO_DATE('20060731235959', 'YYYYMMDDHH24MISS') \n");

        try {
            conn = ConnectionManager.getConnection();
            conn.setAutoCommit(true);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sQuery.toString());

            oswFile = new OutputStreamWriter(new FileOutputStream(fileNm1, false), "ISO-8859-1");

            sbTemp.append("ID"         ).append("|")
                  .append("FIRST NAME" ).append("|")
                  .append("LAST NAME"  ).append("|")
                  .append("GENDER"     ).append("|")
                  .append("BIRTHDAY"   ).append("|")
                  .append("EMAIL"      ).append("|")
                  .append("NEWS_LETTER").append("|")
                  .append("HP_BRAND"   ).append("|")
                  .append("HP_MODEL"   ).append("|")
                  .append("HP_IMEI"    ).append("|")
                  .append("HP_NUM"     ).append("|")
                  .append("HP_PROVIDER").append("|")
                  .append("HP_BUY_YM"  ).append("|")
                  .append("GRADE"      ).append("|")
                  .append("CREATED"    ).append("|")
                  .append("MODIFIED"   ).append("|")
                  .append("DELETED"    ).append("|")
                  .append("POINT"      ).append("|")
                  .append("POWER"      ).append("|")
                  .append("HP_SN"      ).append("|")
                  .append("RESERVED3"  ).append("\n");

            //oswFile.write(sbTemp.toString());

            while(rs.next()){
                rsmd = rs.getMetaData();
                rsCnt = rsmd.getColumnCount();

                sbTemp.setLength(0);

                for(int i=1;i<=rsCnt;i++) {
                    sbTemp.append(ObjectUtils.toString(rs.getObject(i), ""));

                    if( i == rsCnt ){
                        sbTemp.append("\n");
                    }
                    else{
                        sbTemp.append("|");
                    }
                }

                oswFile.write(sbTemp.toString());
                cnt++;

                if(cnt % 500 == 0){
                    Logger.getLogger("makefile.makeUserFileForFra").info("[MEMBER]here we are == " + cnt);
                }
            }

            Logger.getLogger("makefile.makeUserFileForFra").info("[MEMBER]Total Count : " + cnt);

            if (rs   != null) try { rs.close(); }   catch (Exception e) { e.printStackTrace(); }
            if (oswFile != null) try { oswFile.close(); } catch (Exception e) { e.printStackTrace(); }
/*
            cnt = 0;
            sQuery.setLength(0);
            sQuery.append("SELECT USER_ID             \n")
                  .append("     , CREATED_YMDHMS      \n")
                  .append("     , POINT_CD            \n")
                  .append("     , PTAMOUNT_NUM        \n")
                  .append("     , PTREMAINS_NUM       \n")
                  .append("     , PWAMOUNT_NUM        \n")
                  .append("     , PWREMAINS_NUM       \n")
                  .append("     , GRANTER_ID          \n")
                  .append("     , GRANT_DESC          \n")
                  .append("  FROM G_FRA_POINT_HISTORY \n");

            rs = stmt.executeQuery(sQuery.toString());
            oswFile = new OutputStreamWriter(new FileOutputStream(fileNm2, false), "ISO-8859-1");

            while(rs.next()){
                rsmd = rs.getMetaData();
                rsCnt = rsmd.getColumnCount();

                sbTemp.setLength(0);

                for(int i=1;i<=rsCnt;i++) {
                    sbTemp.append(ObjectUtils.toString(rs.getObject(i), ""));

                    if( i == rsCnt ){
                        sbTemp.append("\n");
                    }
                    else{
                        sbTemp.append("|");
                    }
                }

                oswFile.write(sbTemp.toString());
                cnt++;

                if(cnt % 500 == 0){
                    Logger.getLogger("makefile.makeUserFileForFra").info("[POINT HISTORY]here we are == " + cnt);
                }
            }

            Logger.getLogger("makefile.makeUserFileForFra").info("[POINT HISTORY]Total Count : " + cnt);

            if (rs   != null) try { rs.close(); }   catch (Exception e) { e.printStackTrace(); }
            if (oswFile != null) try { oswFile.close(); } catch (Exception e) { e.printStackTrace(); }

            cnt = 0;
            sQuery.setLength(0);
            sQuery.append("SELECT POINT_CD         \n")
                  .append("     , POINT_NM         \n")
                  .append("     , POINT_NUM        \n")
                  .append("     , POWER_NUM        \n")
                  .append("  FROM G_CO_POINT_VALUE \n")
                  .append(" WHERE SITE_CD = 'fra'  \n");

            rs = stmt.executeQuery(sQuery.toString());
            oswFile = new OutputStreamWriter(new FileOutputStream(fileNm3, false), "ISO-8859-1");

            while(rs.next()){
                rsmd = rs.getMetaData();
                rsCnt = rsmd.getColumnCount();

                sbTemp.setLength(0);

                for(int i=1;i<=rsCnt;i++) {
                    sbTemp.append(ObjectUtils.toString(rs.getObject(i), ""));

                    if( i == rsCnt ){
                        sbTemp.append("\n");
                    }
                    else{
                        sbTemp.append("|");
                    }
                }

                oswFile.write(sbTemp.toString());
                cnt++;

                if(cnt % 500 == 0){
                    Logger.getLogger("makefile.makeUserFileForFra").info("[POINT TABLE]here we are == " + cnt);
                }
            }

            Logger.getLogger("makefile.makeUserFileForFra").info("[POINT TABLE]Total Count : " + cnt);

            if (rs   != null) try { rs.close(); }   catch (Exception e) { e.printStackTrace(); }
            if (oswFile != null) try { oswFile.close(); } catch (Exception e) { e.printStackTrace(); }

            cnt = 0;
            sQuery.setLength(0);
            sQuery.append("SELECT B.ID                                           \n")
                  .append("     , B.HP_MODEL                                     \n")
                  .append("     , B.HP_IMEI                                      \n")
                  .append("     , B.HP_BUY_YM                                    \n")
                  .append("     , TO_CHAR(B.CREATED, 'YYYYMMDDHH24MISS') CREATED \n")
                  .append("     , B.HP_SN                                        \n")
                  .append("  FROM G_MB_MEMBER A                                  \n")
                  .append("     , G_MB_BUY_HISTORY B                             \n")
                  .append(" WHERE A.ID = B.ID                                    \n")
                  .append("   AND A.REG_COUNTRY = 'fra'                          \n");

            rs = stmt.executeQuery(sQuery.toString());
            oswFile = new OutputStreamWriter(new FileOutputStream(fileNm4, false), "ISO-8859-1");

            while(rs.next()){
                rsmd = rs.getMetaData();
                rsCnt = rsmd.getColumnCount();

                sbTemp.setLength(0);

                for(int i=1;i<=rsCnt;i++) {
                    sbTemp.append(ObjectUtils.toString(rs.getObject(i), ""));

                    if( i == rsCnt ){
                        sbTemp.append("\n");
                    }
                    else{
                        sbTemp.append("|");
                    }
                }

                oswFile.write(sbTemp.toString());
                cnt++;

                if(cnt % 500 == 0){
                    Logger.getLogger("makefile.makeUserFileForFra").info("[BUY HISTORY]here we are == " + cnt);
                }
            }

            Logger.getLogger("makefile.makeUserFileForFra").info("[BUY HISTORY]Total Count : " + cnt);
*/
        }
        catch(Exception ex) {
            ex.printStackTrace();

            Logger.getLogger("makefile.makeUserFileForFra").info(ex.toString());
            StackTraceElement[] stElement = ex.getStackTrace();
            for(int p=0; p<stElement.length;p++){
                Logger.getLogger("makefile.makeUserFileForFra").info(stElement[p].toString());
            }
        }
        finally {
            if (rs   != null) try { rs.close(); }   catch (Exception e) { e.printStackTrace(); }
            if (stmt != null) try { stmt.close(); } catch (Exception e) { e.printStackTrace(); }
            if (conn != null) try { conn.close(); } catch (Exception e) { e.printStackTrace(); }
            if (oswFile != null) try { oswFile.close(); } catch (Exception e) { e.printStackTrace(); }
        }
    }
}